package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.News;

import com.ysd.util.DBManager;

public class NewsDaoImpl {
	public List<News> getNews(int cId) {
		List<News> newsList = new ArrayList<News>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * FROM news where newsCategoryId ="+cId);
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
//				String newsContent = newsContents.substring(1,10)+"...";
				int newsCategoryId = rs.getInt("newsCategoryId");
				
				
				News news = new News(newsId, newsTitle, newsContent, newsCategoryId);
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
}
	public List<News> getNews1(int nId) {
		List<News> newsList = new ArrayList<News>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * FROM news where newsId ="+nId);
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				
				News news = new News(newsId, newsTitle, newsContent, newsCategoryId);
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
}
	
	public List<News> getNewsId(int Id) {
		List<News> newsList = new ArrayList<News>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from news where newsId > "+Id+" limit 1");
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				
				News news = new News(newsId, newsTitle, newsContent, newsCategoryId);
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	
	public List<News> getNewsById(int bId) {
		List<News> newsList = new ArrayList<News>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from news where newsId < "+bId+" limit 1");
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				
				News news = new News(newsId, newsTitle, newsContent, newsCategoryId);
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public List<News> getNewsBynId() {
		List<News> newsList = new ArrayList<News>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("SELECT newsId, newsTitle, SUBSTRING(newsContent,1,50) newsContent FROM news ORDER BY RAND() LIMIT 5");
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
				//int newsCategoryId = rs.getInt("newsCategoryId");
				
				News news = new News(newsId, newsTitle, newsContent);
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	public List<News> getAllnewss() {
		List<News> newsList = new ArrayList<News>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("SELECT *FROM news");
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				
				News news = new News(newsId, newsTitle, newsContent, newsCategoryId);
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	//查询所有新闻
	public List<News> getAllNews(News n,int page,int size) {
		List<News> newsList = new ArrayList<News>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			String sql = "SELECT n.*,nc.* FROM news n,newscategory nc where 1=1 ";
			if(n.getNewsId()!=0) {
				sql +=" and newsId ="+n.getNewsId();
			}
			if(n.getNewsTitle()!=null && !n.getNewsTitle().equals("")) {
				sql +=" and newsTitle like '%"+n.getNewsTitle()+"%'";
			}
			if(n.getNewsContent()!=null && !n.getNewsContent().equals("")) {
				sql +=" and newsContent like '%"+n.getNewsContent()+"%'";
			}
			if(n.getNewsCategoryId()!=0) {
				sql += " and n.newsCategoryId="+n.getNewsCategoryId();
			}
			sql+=" and n.newsCategoryId=nc.newsCategoryId";
			
			if(page>0) {
				sql +=" limit "+(page-1)*size+","+size;
			}
			System.out.println(sql);
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsName = rs.getString("newsCategoryName");
				
				News news = new News(newsId, newsTitle, newsContent, newsCategoryId,newsName);
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	 public int delnewsId(int newId) {
		 return DBManager.executeUpdate("DELETE from news WHERE newsId ="+newId);
		 
	 }
	 
	 /**
 	  *  添加产品分类
	 * @param ne
	 * @return返回受影响行数
	 */
	public int addnews(News ne) {
		System.out.println(ne);
		return DBManager.executeUpdate("insert into news VALUES('"+ne.getNewsId()+"','"+ne.getNewsTitle()+"','"+ne.getNewsContent()+"','"+ne.getNewsCategoryId()+"')");	
		
	}
	//返回单个对象是因为查到的结果集只有可能是一条
	public News getNewsId2(int newsssId) {
		News pcc=null;
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			//rs = st.executeQuery("SELECT * FROM news where newsId="+newsssId);
			String sql = "SELECT * FROM news where newsId="+newsssId;
			System.out.println(sql);
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				pcc=new News(newsId, newsTitle, newsContent, newsCategoryId);
			
			}
			System.out.println(pcc);
			return pcc;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
		
	}
	
	public int upNewsId(News ns) {
		String sql=("UPDATE news set newsTitle='"+ns.getNewsTitle()+"',newsContent='"+ns.getNewsContent()+"',newsCategoryId='"+ns.getNewsCategoryId()+"' WHERE newsId="+ns.getNewsId());
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
	 public int delNewsByid(String ids) {
		 return DBManager.executeUpdate("DELETE from news WHERE newsId in("+ids+")");
		 
	 }
}
